-- @owner: @zou_jialiang050
-- @date: 2022-04-26
-- @testpoint: package body定义带commit/rollback的存储过程,合理报错
-- @modified by @kyeleze at 2023/05/04 代码变更优化用例期望值

--step1:声明包1 expect:成功
create or replace package p_package_0027_01 authid definer is
var1 int:=1;
var2 int:=2;
procedure p_package_0027_01();
end p_package_0027_01;
/

--step2:定义包体1,存储过程含commit expect:成功
create or replace package body p_package_0027_01 is
procedure p_package_0027_01()
is
begin
start transaction;
drop table if exists t_package_0027;
create table t_package_0027(c_int int);
insert into t_package_0027 values(var1),(var2);
commit;
end;
end p_package_0027_01;
/

--step3:调用包1中存储过程 expect:合理报错
call p_package_0027_01.p_package_0027_01();

--step4:声明包2 expect:成功
create or replace package p_package_0027_02 authid definer is
var1 int:=1;
var2 int:=2;
procedure p_package_0027_02();
end p_package_0027_02;
/

--step5:定义包体2,存储过程含rollback expect:成功
create or replace package body p_package_0027_02 is
procedure p_package_0027_02()
is
begin
start transaction;
drop table if exists t_package_0027;
create table t_package_0027(c_int int);
insert into t_package_0027 values(var1),(var2);
rollback;
end;
end p_package_0027_02;
/

--step6:调用包2中存储过程 expect:合理报错
call p_package_0027_02.p_package_0027_02();

--step7:清理环境 expect:成功
drop package p_package_0027_01;
drop package p_package_0027_02;